package com.imddy.spbatch001.utils.sql;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class SqlBuilder {
    private static final Logger log = LoggerFactory.getLogger(SqlBuilder.class);

    private String tableName;
    private final List<String> selectFields = new ArrayList<>();
    private final List<String> groupByFields = new ArrayList<>();
    private final List<Object> whereConditions = new ArrayList<>();
    private final List<OrderByItem> orderByItems = new ArrayList<>();
    private final List<String> joinClauses = new ArrayList<>();

    private boolean distinct = false;
    private Integer limit = null;
    private Integer offset = null;

    // ====== 构建方法 ======

    public SqlBuilder table(String tableName) {
        this.tableName = tableName;
        return this;
    }

    public SqlBuilder select(String... fields) {
        selectFields.addAll(Arrays.asList(fields));
        return this;
    }

    public SqlBuilder selectAs(String field, String alias) {
        selectFields.add(field + " AS " + alias);
        return this;
    }

    public SqlBuilder groupBy(String... fields) {
        groupByFields.addAll(Arrays.asList(fields));
        return this;
    }

    public SqlBuilder where(String field, String operator, Object value) {
        whereConditions.add(new Condition(field, operator, value, true));
        return this;
    }

    public SqlBuilder where(Condition condition) {
        whereConditions.add(condition);
        return this;
    }

    public SqlBuilder where(ConditionGroup group) {
        whereConditions.add(group);
        return this;
    }

    public SqlBuilder orderBy(String column, OrderByType type) {
        if (column != null && !column.trim().isEmpty()) {
            orderByItems.add(new OrderByItem(column, type));
        }
        return this;
    }

    public SqlBuilder orderByAsc(String column) {
        return orderBy(column, OrderByType.ASC);
    }

    public SqlBuilder orderByDesc(String column) {
        return orderBy(column, OrderByType.DESC);
    }

    public SqlBuilder join(String joinClause) {
        joinClauses.add(joinClause);
        return this;
    }

    public SqlBuilder withDistinct(boolean distinct) {
        this.distinct = distinct;
        return this;
    }

    public SqlBuilder limit(int limit) {
        this.limit = limit;
        return this;
    }

    public SqlBuilder offset(int offset) {
        this.offset = offset;
        return this;
    }

    // ====== 构建 SQL 和参数 ======

    public SqlStatement build() {
        if (tableName == null || tableName.isEmpty()) {
            throw new IllegalArgumentException("表名不能为空");
        }

        StringBuilder sql = new StringBuilder("SELECT ");
        if (distinct) {
            sql.append("DISTINCT ");
        }

        if (selectFields.isEmpty()) {
            sql.append("*");
        } else {
            sql.append(String.join(", ", selectFields));
        }

        sql.append(" FROM ").append(tableName);

        // JOIN 子句
        if (!joinClauses.isEmpty()) {
            for (String join : joinClauses) {
                sql.append(" ").append(join);
            }
        }

        // WHERE 条件
//        if (!whereConditions.isEmpty()) {
//            sql.append(" WHERE ");
//            List<String> conditions = new ArrayList<>();
//            for (Condition cond : whereConditions) {
//                conditions.add(cond.getField() + " " + cond.getOperator() + " ?");
//            }
//            sql.append(String.join(" AND ", conditions));
//        }
        // WHERE 条件
//        if (!whereConditions.isEmpty()) {
//            sql.append(" WHERE ");
//            List<String> conditions = new ArrayList<>();
//            for (Condition cond : whereConditions) {
//                String valuePart;
//                if (cond.getValue() instanceof String) {
//                    valuePart = "'" + escape((String) cond.getValue()) + "'";
//                } else {
//                    valuePart = cond.getValue().toString();
//                }
//                conditions.add(cond.getField() + " " + cond.getOperator() + " " + valuePart);
//            }
//            sql.append(String.join(" AND ", conditions));
//        }
        // WHERE 条件
        if (!whereConditions.isEmpty()) {
            sql.append(" WHERE ");
            List<String> conditions = new ArrayList<>();
            for (Object cond : whereConditions) {
                if (cond instanceof Condition condition) {
                    conditions.add(buildConditionByQuestionMark(condition));
                } else if (cond instanceof ConditionGroup group) {
                    List<String> groupParts = new ArrayList<>();
                    for (Object nestedCond : group.getConditions()) {
                        if (nestedCond instanceof Condition) {
                            groupParts.add(buildConditionByQuestionMark((Condition) nestedCond));
                        } else {
                            log.info("ConditionGroup 中只能包含 Condition");
                            log.error("ConditionGroup 中只能包含 Condition");
                            throw new IllegalArgumentException("ConditionGroup 中只能包含 Condition");
                        }
                    }
                    if (!groupParts.isEmpty()) {
                        String groupSql = String.join(" " + group.getCombinator() + " ", groupParts);
                        if (group.isParenWrapped()) {
                            groupSql = "(" + groupSql + ")";
                        }
                        conditions.add(groupSql);
                    }
                } else {
                    log.info("where 条件必须是 Condition 或 ConditionGroup");
                    log.error("where 条件必须是 Condition 或 ConditionGroup");
                    throw new IllegalArgumentException("where 条件必须是 Condition 或 ConditionGroup");
                }
            }
            sql.append(String.join(" AND ", conditions));
        }

        // GROUP BY
        if (!groupByFields.isEmpty()) {
            sql.append(" GROUP BY ").append(String.join(", ", groupByFields));
        }

        // ORDER BY
        if (!orderByItems.isEmpty()) {
            sql.append(" ORDER BY ");
            List<String> orderBys = new ArrayList<>();
            for (OrderByItem item : orderByItems) {
                orderBys.add(item.toSql());
            }
            sql.append(String.join(", ", orderBys));
        }

        // LIMIT/OFFSET
        if (limit != null) {
            sql.append(" LIMIT ").append(limit);
            if (offset != null) {
                sql.append(" OFFSET ").append(offset);
            }
        }

        // 构造参数列表
        List<Object> parameters = new ArrayList<>();
        for (Object cond : whereConditions) {
            if (cond instanceof Condition c && c.hasValue()) {
                parameters.add(c.getValue());
            } else if (cond instanceof ConditionGroup g) {
                for (Object obj : g.getConditions()) {
                    if (obj instanceof Condition c && c.hasValue()) {
                        parameters.add(c.getValue());
                    }
                }
            }
        }
        return new SqlStatement(sql.toString(), parameters);
    }

    // 转义单引号防止 SQL 注入（构建预览时使用）
    private String escape(String value) {
        return value.replace("'", "''");
    }

    //构建条件(SQL?)条件
    private String buildConditionByQuestionMark(Condition condition) {
        if (!condition.hasValue()) {
            return condition.getField() + " " + condition.getOperator();
        }
        return condition.getField() + " " + condition.getOperator() + " ?";
    }

    //构建条件(SQL值)条件
    private String buildConditionByValueMark(Condition condition) {
        Object rawValue = condition.getValue();
        String valuePart;

        if (rawValue == null || !condition.hasValue()) {
            return condition.getField() + " " + condition.getOperator();
        }

        if (rawValue instanceof RawExpression) {
            valuePart = ((RawExpression) rawValue).getExpression();
        } else if (rawValue instanceof String) {
            valuePart = "'" + escape((String) rawValue) + "'";
        } else if (rawValue instanceof Number || rawValue instanceof Boolean) {
            valuePart = rawValue.toString();
        } else {
            valuePart = "'" + escape(rawValue.toString()) + "'";
        }

        return condition.getField() + " " + condition.getOperator() + " " + valuePart;
    }






    // ====== 内部类：SQL语句和参数 ======
    public static class SqlStatement {
        private final String sql;
        private final List<Object> parameters;

        public SqlStatement(String sql, List<Object> parameters) {
            this.sql = sql;
            this.parameters = parameters;
        }

        public String getSql() {
            return sql;
        }

        public List<Object> getParameters() {
            return parameters;
        }

//        @Override
//        public String toString() {
//            return "SQL: " + sql + "\n参数: " + parameters;
//        }
        /**
         * 生成带参数值的完整 SQL（用于调试）
         */
        public String getFullSql() {
            StringBuilder fullSql = new StringBuilder();
            int paramIndex = 0;
            int length = sql.length();

            for (int i = 0; i < length; i++) {
                char c = sql.charAt(i);
                if (c == '?' && paramIndex < parameters.size()) {
                    Object value = parameters.get(paramIndex++);
                    if (value instanceof String) {
                        fullSql.append("'").append(escape((String) value)).append("'");
                    } else if (value instanceof Number || value instanceof Boolean) {
                        fullSql.append(value);
                    } else if (value instanceof RawExpression) {
                        fullSql.append(value);
                    } else if (value instanceof List<?>) {
                        List<?> list = (List<?>) value;
                        StringBuilder sb = new StringBuilder("(");
                        for (int j = 0; j < list.size(); j++) {
                            Object item = list.get(j);
                            if (item instanceof String) {
                                sb.append("'").append(escape((String) item)).append("'");
                            } else if (item instanceof Number || item instanceof Boolean) {
                                sb.append(item);
                            } else if (item instanceof RawExpression) {
                                sb.append(item);
                            } else if (item == null) {
                                sb.append("NULL");
                            } else {
                                sb.append("'").append(escape(item.toString())).append("'");
                            }
                            if (j < list.size() - 1) sb.append(", ");
                        }
                        sb.append(")");
                        fullSql.append(sb.toString());
                    } else {
                        fullSql.append("'").append(value.toString()).append("'");
                    }
                } else {
                    fullSql.append(c);
                }
            }
            return fullSql.toString();
        }

        /**
         * 转义单引号，防止 SQL 注入
         */
        private String escape(String value) {
            return value.replace("'", "''");
        }

        @Override
        public String toString() {
            return "SQL: " + sql + "\n参数: " + parameters + "\n完整SQL: " + getFullSql();
        }

    }


//    private static class OrderByItem {
//        private final String column;
//        private final OrderByType type;
//
//        public OrderByItem(String column, OrderByType type) {
//            this.column = column;
//            this.type = type;
//        }
//
//        public String toSql() {
//            return column + " " + type;
//        }
//    }

}
